ESOP & Option Pool Modeler: Vesting, IFRS 2, Dilution – Excel XLSX

This Excel model turns a list of option grants into the four things a company actually needs from its share plan.

A vesting schedule that handles cliffs and leavers correctly. The share-based payment expense an auditor will ask for. An honest view of how much of the pool is left. And what the options are worth to the people holding them at an exit. Every figure is a live formula.

THE POOL QUESTION, ANSWERED HONESTLY

Most option trackers report the pool as granted against authorised and stop there. That number is wrong the moment somebody leaves. Forfeited options return to the pool and can be granted again; exercised options leave the pool permanently and become ordinary shares. This model tracks all four states and reports what is genuinely available.

It then asks the question a founder needs answered before the next hiring round: enter the options you expect to grant over the next twelve months, and the model tells you whether the pool survives it, how large a top-up you need, and what that top-up costs in dilution. The dilution block shows the pool three ways, counting the whole authorised pool, counting only what has been granted, and counting only what has actually vested. Investors quote the first, employees feel the third, and the gap between them is worth understanding before a negotiation.

VESTING, CLIFFS AND LEAVERS

A grant vests nothing before its cliff. At the cliff the whole elapsed portion vests at once, and then vesting continues monthly to the end of the vesting period. Advisor grants with no cliff vest from month one. A leaver stops vesting in their leave month and forfeits everything unvested, which returns to the pool. Four integrity checks police exactly this: vesting never exceeds the grant, vested plus forfeited equals granted grant by grant, nothing vests before its cliff, and vesting never goes backwards for anyone who stayed.

FAIR VALUE AND THE EXPENSE YOUR AUDITOR ASKS FOR

IFRS 2 and ASC 718 both require the grant-date fair value of an option to be charged over the period the employee earns it, whether or not it is ever exercised, and whether or not it ends up in the money. That fair value is not the intrinsic spread, which is usually zero at grant. It is an option pricing value.

This model computes a Black-Scholes value for every grant from the scenario's volatility, risk-free rate and expected term, against the fair market value at that grant's own date. The expense is recognised straight-line over the vesting period and trued up when someone leaves, which is why the monthly expense line occasionally goes negative, and why that is correct rather than a bug. An integrity check confirms every fair value is at least the intrinsic value at grant, the arbitrage bound any correct option value must respect.

WHAT IS INSIDE

Nine tabs, forty grant rows, sixty months of vesting, three scenarios from one cell, and 3,400 live formulas. No macros, no add-ins, no external links, no password protection and no locked cells. A worked example runs through the whole file: twenty-eight grants across seven teams including two leavers, on a two-million-option pool that ends up eighty-two percent committed. A five-page PDF guide documents every convention and every limitation.

Twelve integrity checks sit on their own tab and must all read PASS.

Share plan accounting and taxation are jurisdiction-specific. This workbook models the arithmetic; it is not accounting, legal or tax advice.